/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package charlie.shop.warehouse;

import java.io.*;
import java.util.List;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import com.charlie.db.ShipmentOrderData;
import java.text.SimpleDateFormat;

/**
 *
 * @author Nabytovych Andrey
 */
public class WriteExcelUsedApachePOI {

    private ShipmentOrderData[] orders;
    private HSSFWorkbook book;
    private HSSFSheet sheet;
    private String[] column;
    private String title;
    private String pageName;
    private SimpleDateFormat dateFormater;

    public WriteExcelUsedApachePOI() {
        dateFormater = new SimpleDateFormat("dd.MM.yyyy");
        
        title = "Shipping order list";
        pageName = "Shipment order";
        column = new String[] {
            "Tracking number",
            "Creation date",
            "Warehouse",
            "Location",
            "Shipping cost",
            "Guitar cost",
            "Cost",
            "Status"
        };
    }
    
    public void toFile(OutputStream stream, List<ShipmentOrderData> orders) throws FileNotFoundException, IOException {
        this.book = new HSSFWorkbook();
        this.sheet = book.createSheet(pageName);
        this.orders = new ShipmentOrderData[orders.size()];
        for (int i = 0; i < this.orders.length; i++) {
            this.orders[i] = orders.get(i);
        }
        
        createSheet();
        book.write(stream);
    }

    private void createSheet() {
        createTitle();
        createTableHeader();
        fillOrdersRow();
        createNewPalette();
    }

    private void createTitle() {
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

        CellStyle style = createStyle("Tahoma", 20, true);
        style.setAlignment(CellStyle.ALIGN_CENTER);

        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(45F);

        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(title);
    }

    private void createTableHeader() {
        CellStyle style = createStyle("Calibri", 11, true);
        style = setColor(style, HSSFColor.GREY_50_PERCENT.index);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBorderTop(CellStyle.BORDER_MEDIUM);

        HSSFRow row = sheet.createRow(1);
        row.setHeightInPoints(20F);
        
        Cell cell;
        for (int i = 0; i < 8; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(setVerticalBorder(style, i));
            cell.setCellValue(column[i]);
        }
    }

    private void fillOrdersRow() {
        CellStyle style = createStyle("Calibri", 11, false);
        CellStyle[] styles = {
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_RIGHT), 0), 3),
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_RIGHT), 1), 3),
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_LEFT), 2), 3),
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_LEFT), 3), 3),
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_RIGHT), 4), 3),
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_RIGHT), 5), 3), 
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_RIGHT), 6), 3),
            setHorizontalBorder(setVerticalBorder(setAlighment(style, CellStyle.ALIGN_CENTER), 7), 3)
        };
        CellStyle[][] columnStyle = new CellStyle[4][8];
        for (int i = 0; i < 4; i++) {
            for (int j = 0; j < 8; j++) {
                columnStyle[i][j] = setColorByOrderStatus(styles[j], i + 1);
            }
        }
        

        Cell cell;
        for (int i = 0; i < orders.length - 1; i++) {
            HSSFRow row = sheet.createRow(i + 2);
            for (int j = 0; j < 8; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(columnStyle[orders[i].getStatusId() - 1][j]);
                insertCell(cell, i, j);
            }
        }
        
        //  last row
        HSSFRow row = sheet.createRow(orders.length + 1);
        for (int j = 0; j < 8; j++) {
            cell = row.createCell(j);
            cell.setCellStyle(setHorizontalBorder(columnStyle[orders[orders.length - 1].getStatusId() - 1][j], orders.length + 1));
            insertCell(cell, orders.length - 1, j);
        }
        
        for (int i = 0; i < 8; i++)
            sheet.autoSizeColumn(i);
    }
    
    private void insertCell(Cell cell, int i, int j) {
        switch (j) {
            case 0:
                cell.setCellValue(orders[i].getSoId().intValue());
                break;
            case 1:
                cell.setCellValue(dateFormater.format(orders[i].getCreationDate()));
                break;
            case 2:
                cell.setCellValue(orders[i].getWarehouseName());
                break;
            case 3:
                cell.setCellValue(orders[i].getLocation());
                break;
            case 4:
                cell.setCellValue(orders[i].getShipingCost().intValue());
                break;
            case 5:
                cell.setCellValue(orders[i].getCost().intValue());
                break;
            case 6:
                cell.setCellValue(orders[i].getCost().intValue() + orders[i].getShipingCost().intValue());
                break;
            default:
                cell.setCellValue(orders[i].getStatus());
        }
    }
    
    private CellStyle setColorByOrderStatus(CellStyle style, int status) {
        CellStyle localStyle;
        
        switch (status) {
            case 1:
                localStyle = setColor(style, HSSFColor.BLUE.index);
                break;
            case 2:
                localStyle = setColor(style, HSSFColor.ORANGE.index);
                break;
            case 3:
                localStyle = setColor(style, HSSFColor.GREEN.index);
                break;
            default:
                localStyle = setColor(style, HSSFColor.RED.index);
                break;
        }
        
        return localStyle;
    }

    private CellStyle setVerticalBorder(CellStyle style, int columnNumber) {
        CellStyle localStyle = cloneStyle(style);

        if (columnNumber == 0) {
            localStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
            localStyle.setBorderRight(CellStyle.BORDER_THIN);
        } else if (columnNumber == 7) {
            localStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
            localStyle.setBorderLeft(CellStyle.BORDER_THIN);
        } else {
            localStyle.setBorderLeft(CellStyle.BORDER_THIN);
            localStyle.setBorderRight(CellStyle.BORDER_THIN);
        }

        return localStyle;
    }

    private CellStyle setHorizontalBorder(CellStyle style, int rowNumber) {
        CellStyle localStyle = cloneStyle(style);

        if (orders.length == 1) {
            localStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
            localStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
        } else if (rowNumber == 2) {
            localStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
            localStyle.setBorderBottom(CellStyle.BORDER_THIN);
        } else if (rowNumber == orders.length + 1) {
            localStyle.setBorderTop(CellStyle.BORDER_THIN);
            localStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
        } else {
            localStyle.setBorderTop(CellStyle.BORDER_THIN);
            localStyle.setBorderBottom(CellStyle.BORDER_THIN);
        }

        return localStyle;
    }

    private CellStyle setColor(CellStyle style, short color) {
        CellStyle localStyle = cloneStyle(style);
        localStyle.setFillForegroundColor(color);
        localStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        return localStyle;
    }

    private CellStyle setAlighment(CellStyle style, short alighment) {
        CellStyle localStyle = cloneStyle(style);
        localStyle.setAlignment(alighment);

        return localStyle;
    }

    private CellStyle createStyle(String fontName, int fontSize, boolean isBold) {
        Font font = book.createFont();
        font.setFontHeightInPoints((short) fontSize);
        font.setFontName(fontName);
        if (isBold) {
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        }

        CellStyle style = book.createCellStyle();
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(font);

        return style;
    }

    private CellStyle cloneStyle(CellStyle style) {
        CellStyle localStyle = createStyle("Calibri", 10, true);
        localStyle.cloneStyleFrom(style);

        return localStyle;
    }

    private void createNewPalette() {
        HSSFPalette palette = book.getCustomPalette();

        palette.setColorAtIndex(HSSFColor.RED.index, (byte) 255, (byte) 51, (byte) 0);
        palette.setColorAtIndex(HSSFColor.GREEN.index, (byte) 153, (byte) 204, (byte) 0);
        palette.setColorAtIndex(HSSFColor.BLUE.index, (byte) 197, (byte) 217, (byte) 241);
        palette.setColorAtIndex(HSSFColor.ORANGE.index, (byte) 255, (byte) 204, (byte) 0);
        palette.setColorAtIndex(HSSFColor.GREY_50_PERCENT.index, (byte) 128, (byte) 128, (byte) 128);
    }
}